package swind.web.service.imp;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;

import javax.annotation.Resource;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.hibernate.SQLQuery;
import org.springframework.beans.BeanUtils;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import swind.common.Excel.ExcelUtils;
import swind.web.dao.IRoleDao;
import swind.web.dao.IScoreDao;
import swind.web.dao.ISubjectDao;
import swind.web.dao.IUserDao;
import swind.web.entities.ExamRole;
import swind.web.entities.ExamSubject;
import swind.web.entities.ExamUser;
import swind.web.exception.ExcelEmptyException;
import swind.web.exception.ExcelFormatException;
import swind.web.exception.ExcelMatchException;
import swind.web.exception.FileEmptyException;
import swind.web.exception.UserExistenceException;
import swind.web.service.ITeacherService;
import swind.web.vo.ErrorMessageResult;
import swind.web.vo.RemindVo;
import swind.web.vo.SimpleUserVo;
import swind.web.vo.SubjectVo;
import swind.web.vo.TeaGetScoreVo;
import swind.web.vo.TeaGetStudentVo;

@Service
@Transactional
public class TeacherServiceImp implements ITeacherService {
	
	@Resource(name="subjectDaoImp")
	ISubjectDao subjectDaoImp;
	
	@Resource(name="userDaoImp")
	IUserDao userDaoImp;
	
	@Resource(name="scoreDaoImp")
	IScoreDao scoreDaoImp;
	
	@Resource(name="roleDaoImp")
	IRoleDao roleDaoImp;
	
	//获取全部科目VO集合
	@Override
	public List<SubjectVo> getAllSubject(){
		return subjectDaoImp.getAllSubject();
	}
	
	//根据科目id获得科目VO
	@Override
	public SubjectVo getSubjectById(int subjectId){
		return subjectDaoImp.getSubjectById(subjectId);
	}
	
	
	//增加科目
	@Override
	public boolean addSubject(SubjectVo subjectVo){
		ExamSubject examSubject=new ExamSubject();
		subjectVo.setSubId(new Integer(0));
		BeanUtils.copyProperties(subjectVo, examSubject);
		subjectDaoImp.save(examSubject);
		return true;
	}
	
	
	//删除科目
	@Override
	public boolean deleteSubject(int subjectId){
		
		Iterator<ExamUser> itu=userDaoImp.loadAll().iterator();
		while(itu.hasNext()){
			ExamUser examUser = itu.next();
			Iterator<ExamSubject> its = examUser.getExamSubjects().iterator();
			while(its.hasNext()){
				ExamSubject examSubject = its.next();
				if(examSubject.getSubId()==subjectId){
					its.remove();
				}
			}
		}
		
		
		subjectDaoImp.deleteByKey(subjectId);
		return true;
	}
	
	
	//更新科目信息
	@Override
	public boolean updateSubject(SubjectVo subjectVo){
		ExamSubject examSubject=new ExamSubject();
		BeanUtils.copyProperties(subjectVo, examSubject);
		subjectDaoImp.update(examSubject);
		return true;
	}
	
	
	//查找关于传入科目的全部学生信息集合,返回前台SimpleUserVo学生含check的集合
	@SuppressWarnings("unchecked")
	@Override
	public List<SimpleUserVo> findSimpleUserBySubjectId(int subjectId){
		List<SimpleUserVo> sUserVoList=new ArrayList<SimpleUserVo>();
		
		//查找全部用户
		List<ExamUser> userList=userDaoImp.loadAll();
		Iterator<ExamUser> userIterator=userList.iterator();
		
		//查找与科目相关的学生信息
		StringBuffer hql=new StringBuffer();
		hql.append("select u.user_id as id,u.user_name as name ");
		hql.append("from exam_user as u ");
		hql.append("left join exam_user_subject as eus on u.user_id=eus.user_id ");
		hql.append("where eus.sub_id ="+subjectId);
		SQLQuery sqlQuery = userDaoImp.createSqlQuery(hql.toString());
		List<Object[]> objectList=sqlQuery.list();
		List<ExamUser> sqlList=new ArrayList<ExamUser>();
		for(Object[] object:objectList){
			sqlList.add(new ExamUser((String)object[0], (String)object[1]));
		}
		
		ExamRole teacherRole = roleDaoImp.get(26);
		while(userIterator.hasNext()){
			//迭代过程删除获取user中的教师user
			ExamUser user=userIterator.next();
			if(user.getExamRoles().contains(teacherRole)){
				userIterator.remove();
				continue;
			}
			
			//设置以关联的学生状态为checked
			SimpleUserVo simpleUserVo = new SimpleUserVo();
			BeanUtils.copyProperties(user, simpleUserVo);
			sUserVoList.add(simpleUserVo);
			if(sqlList.contains(user)){
				simpleUserVo.setSubjectCheck(true);
			}
		}
		
		
		return sUserVoList;
	}
	
	//设置学生的科目信息(增加&删除，由service此入口开始，错误方便回滚)
	@Override
	public boolean setSubject(int subjectId,String[] checkedList,String[] notCheckedList){
		if((checkedList==null)&&(notCheckedList!=null)){
			this.setSubjectDelete(subjectId,notCheckedList);
			return true;
			
		}else if((checkedList!=null)&&(notCheckedList==null)){
			this.setSubjectAdd(subjectId,checkedList);
			return true;
			
		}else if((checkedList!=null)&&(notCheckedList!=null)){
			this.setSubjectAdd(subjectId,checkedList);
			this.setSubjectDelete(subjectId,notCheckedList);
			return true;
		}
		
		
		return false;
	}
	
	//增加学生的科目信息
	@Override
	public void setSubjectAdd(int subjectId,String[] checkedList){
		ExamSubject sub=subjectDaoImp.get(subjectId);
		for(String userId:checkedList){
			ExamUser user=userDaoImp.get(userId);
			Set<ExamSubject> subSet=user.getExamSubjects();
			if(!subSet.isEmpty()){
				if(!subSet.contains(sub)){
					subSet.add(sub);
				}
			}else if(subSet.isEmpty()){
				subSet.add(sub);
			}
		}
		
	}
	
	//删除学生的科目信息
	@Override
	public void setSubjectDelete(int subjectId,String[] notCheckedList){
		ExamSubject sub=subjectDaoImp.get(subjectId);
		for(String userId:notCheckedList){
			ExamUser user=userDaoImp.get(userId);
			Set<ExamSubject> subSet=user.getExamSubjects();
			if(!subSet.isEmpty()){
				if(subSet.contains(sub)){
					subSet.remove(sub);
				}
			}
		}
		
	}
	
	@SuppressWarnings({ "unchecked" })
	@Override
	public List<TeaGetScoreVo> queryScoreByData(String[] queryDatas) throws ParseException{
		
		
		/*String subjectName = queryData[0];
		String userId = queryData[1];
		String userName = queryData[2];*/
		
		/*String examTime = queryData[3];
		SimpleDateFormat sf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		Date examTimeT = sf.parse(examTime);*/
		List<TeaGetScoreVo> voList = null;
		
		for(String queryData:queryDatas){
			StringBuffer hql=new StringBuffer();
			hql.append("select score.id as id");
			hql.append(",score.user_id as userId,score.subject_id as subjectId");
			hql.append(",score.exam_time as examTime,score.score as score");
			hql.append(",user.user_name as userName,subject.name as subjectName ");
			hql.append("from exam_score as score ");
			hql.append("left join exam_user as user on user.user_id = score.user_id ");
			hql.append("left join exam_subject as subject on score.subject_id = subject.sub_id ");
			hql.append("where subject.name like '%"+queryData+"%' ");
			hql.append("or score.user_id like '%"+queryData+"%' ");
			hql.append("or user.user_name like '%"+queryData+"%' ");
			//hql.append("and score.exam_time ="+examTimeT);
			SQLQuery sqlQuery = userDaoImp.createSqlQuery(hql.toString());
			List<Object[]> list = sqlQuery.list();
			voList=new ArrayList<TeaGetScoreVo>();
			for(Object[] obj:list){
				TeaGetScoreVo tgsVo = new TeaGetScoreVo();
				tgsVo.setId(String.valueOf(obj[0]));
				tgsVo.setUserId((String)obj[1]);
				tgsVo.setSubjectId(String.valueOf(obj[2]));
				tgsVo.setExamTime(((Date)obj[3]).toString());
				tgsVo.setScore(String.valueOf(obj[4]));
				tgsVo.setUserName((String)obj[5]);
				tgsVo.setSubjectName((String)obj[6]);
				voList.add(tgsVo);
			}
		}
		
		return voList;
	}
	
	@SuppressWarnings("unchecked")
	@Override
	public RemindVo getQuertRemind(String remindValue){
		RemindVo remindVo = new RemindVo();
		remindVo.setRemindValue(remindValue);
		
		List<String> subjectList= subjectDaoImp.createQuery("select sub.name from ExamSubject sub where sub.name like '%"+remindValue+"%'").list();
		List<String> numList= subjectDaoImp.createQuery("select user.userId from ExamUser user where user.userId like '%"+remindValue+"%'").list();
		List<String> userList= subjectDaoImp.createQuery("select user.userName from ExamUser user where user.userName like '%"+remindValue+"%'").list();
		//List<String> timeList= subjectDaoImp.createQuery("select sub.name from ExamSubject sub where sub.name like '%"+remindValue+"%'").list();
		
		remindVo.setSubjectList(subjectList);
		remindVo.setNumList(numList);
		remindVo.setNameList(userList);
		
		return remindVo;
	}

	@Override
	public List<TeaGetStudentVo> getAllStudent() {
		List<TeaGetStudentVo> stuVoList = new ArrayList<TeaGetStudentVo>();
		List<ExamUser> userList=userDaoImp.loadAll();
		Iterator<ExamUser> userIterator=userList.iterator();
		
		ExamRole teacherRole = roleDaoImp.get(26);
		while(userIterator.hasNext()){
			//迭代过程删除获取user中的教师user
			ExamUser user=userIterator.next();
			if(user.getExamRoles().contains(teacherRole)){
				userIterator.remove();
				continue;
			}
			TeaGetStudentVo stuVo = new TeaGetStudentVo();
			BeanUtils.copyProperties(user, stuVo);
			
			SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
			stuVo.setStrbirthday(df.format(user.getBirthday()));
			
			stuVoList.add(stuVo);
		}
		return stuVoList;
	}

	@Override
	public void addStudent(TeaGetStudentVo getStudentVo) throws UserExistenceException,ParseException{
		// TODO Auto-generated method stub
		
		if((getStudentVo.getUserId()!=null)||(getStudentVo.getUserId()!="")){
			if(userDaoImp.get(getStudentVo.getUserId())!=null){
				throw new UserExistenceException();
			}
		}
		
		userDaoImp.save(getStudentVo);
		
	}

	@Override
	public TeaGetStudentVo getTeaStuVoById(String sutdentId) {
		ExamUser user=userDaoImp.get(sutdentId);
		TeaGetStudentVo stuVo=new TeaGetStudentVo();
		BeanUtils.copyProperties(user, stuVo);
		
		SimpleDateFormat df = new SimpleDateFormat("MM/dd/yyyy");
		stuVo.setStrbirthday(df.format(user.getBirthday()));
		
		return stuVo;
	}

	@Override
	public void updateStudent(TeaGetStudentVo getStudentVo) {
		ExamUser user = userDaoImp.get(getStudentVo.getUserId());
		BeanUtils.copyProperties(getStudentVo, user);
		
		SimpleDateFormat df = new SimpleDateFormat("MM/dd/yyyy");
		try {
			user.setBirthday(df.parse(getStudentVo.getStrbirthday()));
		} catch (ParseException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	
	
	@Override
	public List<TeaGetStudentVo> listStudents(Workbook book) throws ExcelMatchException, NoSuchFieldException, IllegalAccessException, ExcelEmptyException {
		Field[] fields = TeaGetStudentVo.class.getDeclaredFields();
		List<String> relationList = new ArrayList<String>(fields.length);
		List<TeaGetStudentVo> studentList = new ArrayList<TeaGetStudentVo>();
		
		for(int s=0;s<book.getNumberOfSheets();s++){
			Sheet sheet = book.getSheetAt(s);
			if(sheet.getRow(0)==null){
				if(s==0){
					throw new ExcelEmptyException();
				}
				break;
			}
			
			Row titleRow = sheet.getRow(0);			
			
			//设置行数与字段的映射关系
			for(int c=0;c<titleRow.getPhysicalNumberOfCells();c++){
				for(Field field:fields){
					if(field.getName().equals(titleRow.getCell(c).getStringCellValue())){
						relationList.add(c, field.getName());
						break;
					}
				}
			}
			
			for(Field field:fields){
				if(!relationList.contains(field.getName())){
					throw new ExcelMatchException();
				}
			}
			
			for(int r=1;r<sheet.getPhysicalNumberOfRows();r++){
				Row row = sheet.getRow(r);
				TeaGetStudentVo student = new TeaGetStudentVo();
				for(int c=0;c<row.getPhysicalNumberOfCells();c++){
					Cell cell = row.getCell(c);
					String fieldtype = student.getClass().getDeclaredField(relationList.get(c)).getType().toString();
					Field field = student.getClass().getDeclaredField(relationList.get(c));
					
					if(fieldtype.equals("class java.lang.String")){
						cell.setCellType(CellType.STRING);
						field.setAccessible(true);
						field.set(student,cell.getStringCellValue());
						
					}else if(fieldtype.equals("class java.lang.Integer")){
						field.setAccessible(true);
						field.set(student,(int)cell.getNumericCellValue());
						
					}else{
						
						
					}
				}
				studentList.add(student);
			}
			
			
		}
		
		
		return studentList;
	}
	
	@Override
	public void fileReceive(MultipartFile[] fileSelect,ErrorMessageResult errorMessageResult){
		
			try {
				if(fileSelect==null||fileSelect[0]==null){
					throw new FileEmptyException();
				}
				if(fileSelect[0].getOriginalFilename()==""){
					throw new FileEmptyException();
				}
			} catch (FileEmptyException e) {
				errorMessageResult.getErrorMessage().put("FileError", e.getMessage());
				errorMessageResult.setSuccess(false);
				return;

			}
		
		
		
		for(MultipartFile upLoadFile:fileSelect){
			
			
				
				String fileName = upLoadFile.getOriginalFilename();
				String format = fileName.split("\\.")[1];
				ExcelUtils excelUtils = new ExcelUtils();
			try {
			
				Workbook book = excelUtils.openWorkBook(upLoadFile.getInputStream(),format);
				List<TeaGetStudentVo> stuList = this.listStudents(book);
				userDaoImp.save(stuList);
				
			} catch (IOException |ParseException | IllegalAccessException  e) {
				errorMessageResult.getErrorMessage().put("normalError", e.getMessage());
				errorMessageResult.setSuccess(false);
				return;
				
			} catch ( NoSuchFieldException | UserExistenceException e) {
				errorMessageResult.getErrorMessage().put("BeanError", e.getMessage());
				errorMessageResult.setSuccess(false);
				return;
				
			} catch (ExcelFormatException |ExcelMatchException |ExcelEmptyException e) {
				errorMessageResult.getErrorMessage().put("ExcelError", e.getMessage());
				errorMessageResult.setSuccess(false);
				return;
				
			}
			
			errorMessageResult.setSuccess(true);
		}
	}

	@Override
	public void deleteStudent(String stuId) {
		userDaoImp.deleteStudentWithRole(stuId);
		
	}
	
	
}
